using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.IO;

namespace MeiluluSqlite
{
    public class DB_SqlLite:IDB
    {
        private static int debug = 0;
        private static string dbpath = "";
        /// <summary>
        /// 创建数据库
        /// </summary>
        public static void CreateDB(string path)
        {
            dbpath = path;
            if (!File.Exists(path))
            {
                SqliteConnection cn = new SqliteConnection("Data Source=" + path);
                cn.Open();
                cn.Close();
            }
        }
        public static bool CreateTable(string path, string strsql, string tableName)
        {
            bool flag = false;
            SqliteConnection cn = new SqliteConnection("data source=" + path);
            if (cn.State != System.Data.ConnectionState.Open)
            {
                cn.Open();
                SqliteCommand cmd = new SqliteCommand();
                cmd.Connection = cn;
                if (HasTable(cn, cmd, tableName) == 0)
                {
                    //cmd.CommandText = "CREATE TABLE Log" + TableNum + "(Log_ID varchar(255),Log_Date varchar(255),Log_Text varchar(255),Log_Type varchar(100),Log_Erro varchar(100))";
                    cmd.CommandText = strsql;
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        flag = true;
                    }
                }
            }
            cn.Close();
            return flag;
        }
        public static int HasTable(SqliteConnection cn, SqliteCommand cmd, string tableName)
        {
            cmd.CommandText = "SELECT count(*) from sqlite_master where type='table' and name='" + tableName + "'";
            return Convert.ToInt32(cmd.ExecuteScalar());
        }
        public static void DelTable(string path, string strsql)
        {
            SqliteConnection cn = new SqliteConnection("data source=" + path);
            if (cn.State != System.Data.ConnectionState.Open)
            {
                cn.Open();
                SqliteCommand cmd = new SqliteCommand();
                cmd.Connection = cn;
                // cmd.CommandText = "DROP TABLE IF EXISTS " + obj[i];
                cmd.CommandText = strsql;
                cmd.ExecuteNonQuery();
                cn.Close();
            }
        }
        public static List<string> SelectTable(string path)
        {
            List<string> tables = new List<string>();
            SqliteConnection cn = new SqliteConnection("data source=" + path);
            if (cn.State != System.Data.ConnectionState.Open)
            {
                cn.Open();
                SqliteCommand cmd = new SqliteCommand();
                cmd.Connection = cn;
                cmd.CommandText = "SELECT name FROM sqlite_master WHERE TYPE='table' ";
                var sr = cmd.ExecuteReader();
                while (sr.Read())
                {
                    tables.Add(sr.GetString(0));
                }

            }
            cn.Close();
            cn.Dispose();
            return tables;
        }
        /// <summary>
        /// 执行数据库语句
        /// </summary>
        /// <param name="value"></param>
        public static bool ExecuteNonQuery(string path, string sqlStr)
        {
            var conn = new SqliteConnection("data source=" + path);
            var flag = TransActionOperate(conn, sqlStr);
            if (debug == 1)
            {
                if (flag)
                {
                    Console.WriteLine("执行成功" + sqlStr);
                }
                else
                {
                    Console.WriteLine("执行失败" + sqlStr);
                }
            }
            return flag;
        }
        static bool TransActionOperate(SqliteConnection cn, string value)
        {
            bool flag = false;
            try
            {
                cn.Open();
                using (SqliteTransaction tr = cn.BeginTransaction())
                {
                    try
                    {
                        var cmd = cn.CreateCommand();
                        cmd.CommandText = value;
                        cmd.ExecuteNonQuery();
                        tr.Commit();
                        flag = true;
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                        tr.Rollback();
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {

                cn.Close();
                cn.Dispose();
            }
            return flag;
        }

        public static List<T> SelectBySql<T>(string path, string sqlstr)
        {
            SqliteConnection cn = new("data source=" + path);
            if (cn.State != System.Data.ConnectionState.Open)
            {
                cn.Open();
                var cmd = cn.CreateCommand();
                cmd.Connection = cn;
                List<T> list = new List<T>();
                var type = typeof(T);
                cmd.CommandText = sqlstr;

                try
                {
                    var sr = cmd.ExecuteReader();
                    while (sr.Read())
                    {
                        T t = Activator.CreateInstance<T>();
                        for (int i = 0; i < sr.FieldCount; i++)
                        {
                            var par = type.GetProperty(sr.GetName(i));
                            par.SetValue(t, Convert.ChangeType(sr.GetValue(i), par.PropertyType));
                        }
                        list.Add(t);
                    }
                    sr.Close();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
                cn.Close();
                cn.Dispose();
                return list;
            }
            cn.Close();
            cn.Dispose();
            return null;
        }
    }
}

